Date Calendar
The Date Calendar allows your readers to select options from date-key columns. They can filter their discoveries or presentations by selecting date "slices"; that is, particular dates, ranges of dates, or formulas based on dates (such as year to date or last x days to date).
Note: You may also be able to slice by dates using drop-downs or buttons (say), where your dates are not modeled as date-key columns. The Date Calendar is only available for date-keys.
You can show or hide any of these pickers from the slicer's settings dialog.
Simple Picker
The Simple Picker allows you to select the items that should be used to filter the query. To select more than one element, select the Multi Select checkbox (purple arrow below). With Multi Select enabled, you can select multiple elements from any level of granularity (orange highlight).
The example below filters the query by the four dates selected: 5, 6, 12, and 13 January, 2008:
Range Picker
Use the range picker to filter the query by a date range, beginning with the From date, up to and including the To date. Select the required level of granularity, then select the start date from the left, and the end date from the right.
The example below filters the query by all dates between 1 April 2009 and 30 September 2009, inclusive:
This is an efficient way to filter the query by a range of dates that doesn't correspond with the granularity. For example, if you want to filter by Q1, you can either select Q1 from the Quarters level in the Simple picker, or you can select the start and end dates for Q1 from the Range picker; the easiest option is the former.
However, if you want to filter by a range of dates within Q1, say, 29 January - 4 March, this is easily achieved using the Range picker.
Formula Picker
Filter the query by a formula based on the selected date element. Start by selecting the granularity level, then select the date element, then choose the required function. (Which functions are available depends on the selected level of granularity.)
The example below filters the query by all dates in 2009 (YTD), up to and including 18 August:
In this dialog, you are filtering the query by a functional selection by choosing the required granularity, date-time period, and function. Pyramid then calculates the formula on-the-fly, without producing any calculation elements (such as those created by the Time Calculation wizard).
Functional Selections
The Formula picker supports the functional selections described below. You can also review detailed documentation about each of these functions here.
YTD
The Year to Date function is available for all granularity levels. It filters the query by all dates or time periods from the beginning of the selected year, up to and including the selected date.
This example will filter the query by all dates in 2009 (YTD), up to and including 18 August:
Here, the granularity is set to months; the selection will filter by all months in 2009, up to and including August:
QTD
The Quarter to Date function is available for levels lower than quarters: months, weeks and dates. It filters the query by all dates or time periods from the beginning of the selected quarter, up to and including the selected element.
Here, the selection filters the query by all dates from the beginning of Q2, 2009, up to and including 19 May:
Now the granularity is changed to months; the selection will filter by all months in Q2 2008, up to and including May:
MTD
The Month to Date function is available for levels lower than months: weeks and dates. It filters the query by all dates or weeks from the beginning of the selected month, up to and including the selected element.
This selection filters the query by all dates from the start of August 2010, up to and including 19 August:
This selection filters the query by all weeks in 2009, up to and including week 2:
WTD
The Week to Date function is available for dates. It filters the query by all dates from the beginning of the selected week, up to and including the selected element.
Here, the query will be filtered by all dates from 12 April 2009 (the start of the week where the selection was made) up to and including 16 April:
Last
The Last function is available for all granularity levels. It filters the query by all dates or periods in the last n time periods, prior to the selected time period.
- Select the Granularity.
- Select the Last function.
- Specify the number of periods to go back.
- Choose the units (type of period) to go back by. (The available selections depend on the selected granularity.)
The example below filters the query by 7 days prior to 25 January, 2008:
In this example, the query is filtered by the 6 months prior to August, 2008:
Function Picker
Filter the query dynamically using a date function that references the "latest date." If you have data in your model up to and including today, the latest date will be the current date / today. If your model only includes data from 2010, say, then it will be the last date for which you have data in 2010.
The example below (YTD) filters the query by the year to date. This means that if the latest date you have data for is today, it will select the data for this year up to and including today:
